#delimit;
clear;
capture log close;
set more off;

local in "/data";
local pathtables "/data";

local out "/data";

use "`in'/rollup_merged_97_14.dta", clear;
sort pid year;

drop teditseq97 teditseq99 teditseq01 person teditseq03 teditseq04 permid PERMID_201006  PERMID_201206  PERMID_201406;
rename long llong;
ren PCTIN903 pctin903;
ren int interest;
label var t "t=1 if data comes from 2010/06 (new data) and 0 if from 2010/12 (old data)";
/*Drop old 2010 data*/
drop if t==0;

label var pctin903 "% median family income, based on 200 census tracts";
/*AUTO LOANS*/;
label var auto_open "# of auto loans opened btw last survey and now";
label var auto_clo "# of auto loans closed btw last survey and now";
label var auto_hist_ontime "# of auto payments current in past 24m";
label var auto_hist_3060 "# of auto payments 30-59 days delinq in past 24m";
label var auto_hist_6090 "# of auto payments 60-89 days delinq in past 24m";
label var auto_hist_90120 "# of auto payments 90-119 days delinq in past 24m";
label var auto_hist_120plus "# of auto payments 120+ days delinq in past 24m";
label var auto_debt "debt in auto loans";
label var auto_num "number of auto loans currently open";

label var auto_rec_ontime "# of auto payments current recently";
label var auto_rec_3060 "# of auto payments 30-59 days delinq recently";
label var auto_rec_6090 "# of auto payments 60-89 days delinq recently";
label var auto_rec_90120 "# of auto payments 90-119 days delinq recently";
label var auto_rec_120plus "# of auto payments 120+ days delinq recently";
label var auto_rec_worse "# of auto payments bad debt/collection recently";

/*STUDENT LOANS*/;
label var student_open "# of std loans opened btw last survey and now";
label var student_clo "# of std loans closed btw last survey and now";
label var student_hist_ontime "# of std payments current in past 24m";
label var student_hist_3060 "# of std payments 30-59 days delinq in past 24m";
label var student_hist_6090 "# of std payments 60-89 days delinq in past 24m";
label var student_hist_90120 "# of std payments 90-119 days delinq in past 24m";
label var student_hist_120plus "# of std payments 120+ days delinq in past 24m";
label var student_debt "debt in std loans";
label var student_num "number of std loans currently open";

label var student_rec_ontime "# of std payments current recently";
label var student_rec_3060 "# of std payments 30-59 days delinq recently";
label var student_rec_6090 "# of std payments 60-89 days delinq recently";
label var student_rec_90120 "# of std payments 90-119 days delinq recently";
label var student_rec_120plus "# of std payments 120+ days delinq recently";
label var student_rec_worse "# of std payments bad debt/collection recently";

/*MORTGAGE DEBT*/;
label var mortgage_open "# of mtg loans opened btw last survey and now";
label var mortgage_clo "# of mtg loans closed btw last survey and now";
label var mortgage_hist_ontime "# of mtg payments current in past 24m";
label var mortgage_hist_3060 "# of mtg payments 30-59 days delinq in past 24m";
label var mortgage_hist_6090 "# of mtg payments 60-89 days delinq in past 24m";
label var mortgage_hist_90120 "# of mtg payments 90-119 days delinq in past 24m";
label var mortgage_hist_120plus "# of mtg payments 120+ days delinq in past 24m";
label var mortgage_debt "debt in mtg loans";
label var mortgage_num "number of mtg loans currently open";

label var mortgage_rec_ontime "# of mtg payments current recently";
label var mortgage_rec_3060 "# of mtg payments 30-59 days delinq recently";
label var mortgage_rec_6090 "# of mtg payments 60-89 days delinq recently";
label var mortgage_rec_90120 "# of mtg payments 90-119 days delinq recently";
label var mortgage_rec_120plus "# of mtg payments 120+ days delinq recently";
label var mortgage_rec_worse "# of mtg payments bad debt/collection recently";

/*****************************/
/*******CREDIT CARD DEBT******/
/*****************************/
/*CREDIT CARD DEBT (unsecured)*/
/*We have many variations for credit card debt. First, we have calculations for Unsecured Credit Card Debt and for Total (Secured+Unsecured)*/
/*
Then we have for unsecured and total, measures for 
1-Individual accounts (ecoa=I), 
2-Joint contractuability (ecoa=C),
3-Joint+Co-signer (liable) (ecoa=C, ecoa=M (cosigner primarilly liable for account), ecoa=S (cosigner, no spousal relationship)),
4-Not liable (ecoa=A (Authorized user of share account, not liable)),
5-May not be liable (econ=A and econ=P (participant in shared account which cannot be distinguished as C or A)).
*/;
/*1-that belong to the individual*/;
label var ccard_indiv_open "# of unsec ind cc accounts opened btw last survey and now";
label var ccard_indiv_clo "# of unsec ind cc closed btw last survey and now";
label var ccard_indiv_hist_ontime "# of unsec ind cc payments current in past 24m";
label var ccard_indiv_hist_3060 "# of unsec ind cc payments 30-59 days delinq in past 24m";
label var ccard_indiv_hist_6090 "# of unsec ind cc payments 60-89 days delinq in past 24m";
label var ccard_indiv_hist_90120 "# of unsec ind cc payments 90-119 days delinq in past 24m";
label var ccard_indiv_hist_120plus "# of unsec ind cc payments 120+ days delinq in past 24m";
label var ccard_indiv_deb "debt in unsec ind cc";
label var ccard_indiv_num "number of unsec ind cc currently open";

label var ccard_indiv_rec_ontime "# of unsec ind cc payments current recently";
label var ccard_indiv_rec_3060 "# of unsec ind cc payments 30-59 days delinq recently";
label var ccard_indiv_rec_6090 "# of unsec ind cc payments 60-89 days delinq recently";
label var ccard_indiv_rec_90120 "# of unsec ind cc payments 90-119 days delinq recently";
label var ccard_indiv_rec_120plus "# of unsec ind cc payments 120+ days delinq recently";
label var ccard_indiv_rec_worse "# of unsec ind cc payments bad debt/collection recently";

/*2-joint account*/;
label var ccard_joint_open "# of unsec joint cc accounts opened btw last survey and now";
label var ccard_joint_clo "# of unsec joint cc closed btw last survey and now";
label var ccard_joint_hist_ontime "# of unsec joint cc payments current in past 24m";
label var ccard_joint_hist_3060 "# of unsec joint cc payments 30-59 days delinq in past 24m";
label var ccard_joint_hist_6090 "# of unsec joint cc payments 60-89 days delinq in past 24m";
label var ccard_joint_hist_90120 "# of unsec joint cc payments 90-119 days delinq in past 24m";
label var ccard_joint_hist_120plus "# of unsec joint cc payments 120+ days delinq in past 24m";
label var ccard_joint_debt "debt in unsec joint cc";
label var ccard_joint_num "number of unsec joint cc currently open";

label var ccard_joint_rec_ontime "# of unsec joint cc payments current recently";
label var ccard_joint_rec_3060 "# of unsec joint cc payments 30-59 days delinq recently";
label var ccard_joint_rec_6090 "# of unsec joint cc payments 60-89 days delinq recently";
label var ccard_joint_rec_90120 "# of unsec joint cc payments 90-119 days delinq recently";
label var ccard_joint_rec_120plus "# of unsec joint cc payments 120+ days delinq recently";
label var ccard_joint_rec_worse "# of unsec joint cc payments bad debt/collection recently";

/*3-co-signer and joint*/;
label var ccard_cosign_open "# of unsec cosign cc accounts opened btw last survey and now";
label var ccard_cosign_clo "# of unsec cosign cc closed btw last survey and now";
label var ccard_cosign_hist_ontime "# of unsec cosign cc payments current in past 24m";
label var ccard_cosign_hist_3060 "# of unsec cosign cc payments 30-59 days delinq in past 24m";
label var ccard_cosign_hist_6090 "# of unsec cosign cc payments 60-89 days delinq in past 24m";
label var ccard_cosign_hist_90120 "# of unsec cosign cc payments 90-119 days delinq in past 24m";
label var ccard_cosign_hist_120plus "# of unsec cosign cc payments 120+ days delinq in past 24m";
label var ccard_cosign_debt "debt in unsec cosign cc";
label var ccard_cosign_num "number of unsec cosign cc currently open";

label var ccard_cosign_rec_ontime "# of unsec cosign cc payments current recently";
label var ccard_cosign_rec_3060 "# of unsec cosign cc payments 30-59 days delinq recently";
label var ccard_cosign_rec_6090 "# of unsec cosign cc payments 60-89 days delinq recently";
label var ccard_cosign_rec_90120 "# of unsec cosign cc payments 90-119 days delinq recently";
label var ccard_cosign_rec_120plus "# of unsec cosign cc payments 120+ days delinq recently";
label var ccard_cosign_rec_worse "# of unsec cosign cc payments bad debt/collection recently";

/*4-Not liable*/;
label var ccard_notliab_open "# of unsec notliab cc accounts opened btw last survey and now";
label var ccard_notliab_clo "# of unsec notliab cc closed btw last survey and now";
label var ccard_notliab_hist_ontime "# of unsec notliab cc payments current in past 24m";
label var ccard_notliab_hist_3060 "# of unsec notliab cc payments 30-59 days delinq in past 24m";
label var ccard_notliab_hist_6090 "# of unsec notliab cc payments 60-89 days delinq in past 24m";
label var ccard_notliab_hist_90120 "# of unsec notliab cc payments 90-119 days delinq in past 24m";
label var ccard_notliab_hist_120plus "# of unsec notliab cc payments 120+ days delinq in past 24m";
label var ccard_notliab_debt "debt in unsec notliab cc";
label var ccard_notliab_num "number of unsec notliab cc currently open";

label var ccard_notliab_rec_ontime "# of unsec notliab cc payments current recently";
label var ccard_notliab_rec_3060 "# of unsec notliab cc payments 30-59 days delinq recently";
label var ccard_notliab_rec_6090 "# of unsec notliab cc payments 60-89 days delinq recently";
label var ccard_notliab_rec_90120 "# of unsec notliab cc payments 90-119 days delinq recently";
label var ccard_notliab_rec_120plus "# of unsec notliab cc payments 120+ days delinq recently";
label var ccard_notliab_rec_worse "# of unsec notliab cc payments bad debt/collection recently";

/*5-May be liable*/;
label var ccard_maybeliab_open "# of unsec maybe cc accounts opened btw last survey and now";
label var ccard_maybeliab_clo "# of unsec maybe cc closed btw last survey and now";
label var ccard_maybeliab_hist_ontime "# of unsec maybe cc payments current in past 24m";
label var ccard_maybeliab_hist_3060 "# of unsec maybe cc payments 30-59 days delinq in past 24m";
label var ccard_maybeliab_hist_6090 "# of unsec maybe cc payments 60-89 days delinq in past 24m";
label var ccard_maybeliab_hist_90120 "# of unsec maybe cc payments 90-119 days delinq in past 24m";
label var ccard_maybeliab_hist_120plus "# of unsec maybe cc payments 120+ days delinq in past 24m";
label var ccard_maybeliab_debt "debt in unsec maybe cc";
label var ccard_maybeliab_num "number of unsec maybe cc currently open";

label var ccard_maybeliab_rec_ontime "# of unsec maybe cc payments current recently";
label var ccard_maybeliab_rec_3060 "# of unsec maybe cc payments 30-59 days delinq recently";
label var ccard_maybeliab_rec_6090 "# of unsec maybe cc payments 60-89 days delinq recently";
label var ccard_maybeliab_rec_90120 "# of unsec maybe cc payments 90-119 days delinq recently";
label var ccard_maybeliab_rec_120plus "# of unsec maybe cc payments 120+ days delinq recently";
label var ccard_maybeliab_rec_worse "# of unsec maybe cc payments bad debt/collection recently";

/*TOTAL CREDIT CARD DEBT (secured+unsecured)*/;
/*1-that belong to the individual*/;
label var cctot_indiv_open "# of total ind cc accounts opened btw last survey and now";
label var cctot_indiv_clo "# of total ind cc closed btw last survey and now";
label var cctot_indiv_hist_ontime "# of total ind cc payments current in past 24m";
label var cctot_indiv_hist_3060 "# of total ind cc payments 30-59 days delinq in past 24m";
label var cctot_indiv_hist_6090 "# of total ind cc payments 60-89 days delinq in past 24m";
label var cctot_indiv_hist_90120 "# of total ind cc payments 90-119 days delinq in past 24m";
label var cctot_indiv_hist_120plus "# of total ind cc payments 120+ days delinq in past 24m";
label var cctot_indiv_debt "debt in total ind cc";
label var cctot_indiv_num "number of total ind cc currently open";

label var cctot_indiv_rec_ontime "# of total ind cc payments current recently";
label var cctot_indiv_rec_3060 "# of total ind cc payments 30-59 days delinq recently";
label var cctot_indiv_rec_6090 "# of total ind cc payments 60-89 days delinq recently";
label var cctot_indiv_rec_90120 "# of total ind cc payments 90-119 days delinq recently";
label var cctot_indiv_rec_120plus "# of total ind cc payments 120+ days delinq recently";
label var cctot_indiv_rec_worse "# of total ind cc payments bad debt/collection recently";

/*2-joint account*/;
label var cctot_joint_open "# of total joint cc accounts opened btw last survey and now";
label var cctot_joint_clo "# of total joint cc closed btw last survey and now";
label var cctot_joint_hist_ontime "# of total joint cc payments current in past 24m";
label var cctot_joint_hist_3060 "# of total joint cc payments 30-59 days delinq in past 24m";
label var cctot_joint_hist_6090 "# of total joint cc payments 60-89 days delinq in past 24m";
label var cctot_joint_hist_90120 "# of total joint cc payments 90-119 days delinq in past 24m";
label var cctot_joint_hist_120plus "# of total joint cc payments 120+ days delinq in past 24m";
label var cctot_joint_debt "debt in total joint cc";
label var cctot_joint_num "number of total joint cc currently open";

label var cctot_joint_rec_ontime "# of total joint cc payments current recently";
label var cctot_joint_rec_3060 "# of total joint cc payments 30-59 days delinq recently";
label var cctot_joint_rec_6090 "# of total joint cc payments 60-89 days delinq recently";
label var cctot_joint_rec_90120 "# of total joint cc payments 90-119 days delinq recently";
label var cctot_joint_rec_120plus "# of total joint cc payments 120+ days delinq recently";
label var cctot_joint_rec_worse "# of total joint cc payments bad debt/collection recently";

/*3-co-signer and joint*/;
label var cctot_cosign_open "# of total cosign cc accounts opened btw last survey and now";
label var cctot_cosign_clo "# of total cosign cc closed btw last survey and now";
label var cctot_cosign_hist_ontime "# of total cosign cc payments current in past 24m";
label var cctot_cosign_hist_3060 "# of total cosign cc payments 30-59 days delinq in past 24m";
label var cctot_cosign_hist_6090 "# of total cosign cc payments 60-89 days delinq in past 24m";
label var cctot_cosign_hist_90120 "# of total cosign cc payments 90-119 days delinq in past 24m";
label var cctot_cosign_hist_120plus "# of total cosign cc payments 120+ days delinq in past 24m";
label var cctot_cosign_debt "debt in total cosign cc";
label var cctot_cosign_num "number of total cosign cc currently open";

label var cctot_cosign_rec_ontime "# of total cosign cc payments current recently";
label var cctot_cosign_rec_3060 "# of total cosign cc payments 30-59 days delinq recently";
label var cctot_cosign_rec_6090 "# of total cosign cc payments 60-89 days delinq recently";
label var cctot_cosign_rec_90120 "# of total cosign cc payments 90-119 days delinq recently";
label var cctot_cosign_rec_120plus "# of total cosign cc payments 120+ days delinq recently";
label var cctot_cosign_rec_worse "# of total cosign cc payments bad debt/collection recently";

/*4-Not liable*/;
label var cctot_notliab_open "# of total notliab cc accounts opened btw last survey and now";
label var cctot_notliab_clo "# of total notliab cc closed btw last survey and now";
label var cctot_notliab_hist_ontime "# of total notliab cc payments current in past 24m";
label var cctot_notliab_hist_3060 "# of total notliab cc payments 30-59 days delinq in past 24m";
label var cctot_notliab_hist_6090 "# of total notliab cc payments 60-89 days delinq in past 24m";
label var cctot_notliab_hist_90120 "# of total notliab cc payments 90-119 days delinq in past 24m";
label var cctot_notliab_hist_120plus "# of total notliab cc payments 120+ days delinq in past 24m";
label var cctot_notliab_debt "debt in total notliab cc";
label var cctot_notliab_num "number of total notliab cc currently open";

label var cctot_notliab_rec_ontime "# of total notliab cc payments current recently";
label var cctot_notliab_rec_3060 "# of total notliab cc payments 30-59 days delinq recently";
label var cctot_notliab_rec_6090 "# of total notliab cc payments 60-89 days delinq recently";
label var cctot_notliab_rec_90120 "# of total notliab cc payments 90-119 days delinq recently";
label var cctot_notliab_rec_120plus "# of total notliab cc payments 120+ days delinq recently";
label var cctot_notliab_rec_worse "# of total notliab cc payments bad debt/collection recently";

/*5-May be liable*/;
label var cctot_maybeliab_open "# of total maybe cc accounts opened btw last survey and now";
label var cctot_maybeliab_clo "# of total maybe cc closed btw last survey and now";
label var cctot_maybeliab_hist_ontime "# of total maybe cc payments current in past 24m";
label var cctot_maybeliab_hist_3060 "# of total maybe cc payments 30-59 days delinq in past 24m";
label var cctot_maybeliab_hist_6090 "# of total maybe cc payments 60-89 days delinq in past 24m";
label var cctot_maybeliab_hist_90120 "# of total maybe cc payments 90-119 days delinq in past 24m";
label var cctot_maybeliab_hist_120plus "# of total maybe cc payments 120+ days delinq in past 24m";
label var cctot_maybeliab_debt "debt in total maybe cc";
label var cctot_maybeliab_num "number of total maybe cc currently open";

label var cctot_maybeliab_rec_ontime "# of total maybe cc payments current recently";
label var cctot_maybeliab_rec_3060 "# of total maybe cc payments 30-59 days delinq recently";
label var cctot_maybeliab_rec_6090 "# of total maybe cc payments 60-89 days delinq recently";
label var cctot_maybeliab_rec_90120 "# of total maybe cc payments 90-119 days delinq recently";
label var cctot_maybeliab_rec_120plus "# of total maybe cc payments 120+ days delinq recently";
label var cctot_maybeliab_rec_worse "# of total maybe cc payments bad debt/collection recently";

/*Agreggating unsecured credit card debt and total credit card debt for individual and joint accounts. The assumption is that joint debt is split in 2*/;
label var ccard_indiv_joint_debt "unsecured ccard debt in indiv and joint accounts";
label var cctot_indiv_joint_debt "total ccard debt in indiv and joint accounts";

/*Labeling some transunion variables without labels*/;
label var block "census block number";
label var county "county";
label var employer "employer name";
label var inc "tract income indicator 1=<80, 2=80-120, 3=120+";
label var llongl "latlongl: b=block, T=tract";
label var llongv "latlongv: Latitude-longitude";
label var min "tract minority level: 1=0-10, 2=11-80, 3=80+";
label var minpct "% minority in tract: based on 2000 census tracts";
label var msa "MSA";
label var occ "Occupation";
label var state "geostate";
label var tract "geocensustract";


/*INSTALLMENT LOANS*/;
label var install_open "# of installment loans opened btw last survey and now";
label var install_clo "# of installment loans closed btw last survey and now";
label var install_hist_ontime "# of installment payments current in past 24m";
label var install_hist_3060 "# of installment payments 30-59 days delinq in past 24m";
label var install_hist_6090 "# of installment payments 60-89 days delinq in past 24m";
label var install_hist_90120 "# of installment payments 90-119 days delinq in past 24m";
label var install_hist_120plus "# of installment payments 120+ days delinq in past 24m";
label var install_debt "debt in installment loans";
label var install_num "number of installment loans currently open";

label var install_rec_ontime "# of installment payments current recently";
label var install_rec_3060 "# of installment payments 30-59 days delinq recently";
label var install_rec_6090 "# of installment payments 60-89 days delinq recently";
label var install_rec_90120 "# of installment payments 90-119 days delinq recently";
label var install_rec_120plus "# of installment payments 120+ days delinq recently";
label var install_rec_worse "# of installment payments bad debt/collection recently";

/*LINE OF CREDIT LOANS*/;
label var lcred_open "# of line of credit loans opened btw last survey and now";
label var lcred_clo "# of line of credit loans closed btw last survey and now";
label var lcred_hist_ontime "# of line of credit payments current in past 24m";
label var lcred_hist_3060 "# of line of credit payments 30-59 days delinq in past 24m";
label var lcred_hist_6090 "# of line of credit payments 60-89 days delinq in past 24m";
label var lcred_hist_90120 "# of line of credit payments 90-119 days delinq in past 24m";
label var lcred_hist_120plus "# of line of credit payments 120+ days delinq in past 24m";
label var lcred_debt "debt in line of credit loans";
label var lcred_num "number of line of credit loans currently open";

label var lcred_rec_ontime "# of line of credit payments current recently";
label var lcred_rec_3060 "# of line of credit payments 30-59 days delinq recently";
label var lcred_rec_6090 "# of line of credit payments 60-89 days delinq recently";
label var lcred_rec_90120 "# of line of credit payments 90-119 days delinq recently";
label var lcred_rec_120plus "# of line of credit payments 120+ days delinq recently";
label var lcred_rec_worse "# of line of credit payments bad debt/collection recently";

/*UNSECURED LOANS*/;
label var unsec_open "# of unsecured loans opened btw last survey and now";
label var unsec_clo "# of unsecured loans closed btw last survey and now";
label var unsec_hist_ontime "# of unsecured payments current in past 24m";
label var unsec_hist_3060 "# of unsecured payments 30-59 days delinq in past 24m";
label var unsec_hist_6090 "# of unsecured payments 60-89 days delinq in past 24m";
label var unsec_hist_90120 "# of unsecured payments 90-119 days delinq in past 24m";
label var unsec_hist_120plus "# of unsecured payments 120+ days delinq in past 24m";
label var unsec_debt "debt in unsecured loans";
label var unsec_num "number of unsecured loans currently open";

label var unsec_rec_ontime "# of unsecured payments current recently";
label var unsec_rec_3060 "# of unsecured payments 30-59 days delinq recently";
label var unsec_rec_6090 "# of unsecured payments 60-89 days delinq recently";
label var unsec_rec_90120 "# of unsecured payments 90-119 days delinq recently";
label var unsec_rec_120plus "# of unsecured payments 120+ days delinq recently";
label var unsec_rec_worse "# of unsecured payments bad debt/collection recently";

/*SECURED GOODS LOANS*/;
label var secgoods_open "# of secured goods loans opened btw last survey and now";
label var secgoods_clo "# of secured goods loans closed btw last survey and now";
label var secgoods_hist_ontime "# of secured goods payments current in past 24m";
label var secgoods_hist_3060 "# of secured goods payments 30-59 days delinq in past 24m";
label var secgoods_hist_6090 "# of secured goods payments 60-89 days delinq in past 24m";
label var secgoods_hist_90120 "# of secured goods payments 90-119 days delinq in past 24m";
label var secgoods_hist_120plus "# of secured goods payments 120+ days delinq in past 24m";
label var secgoods_debt "debt in secured goods loans";
label var secgoods_num "number of secured goods loans currently open";

label var secgoods_rec_ontime "# of secured goods payments current recently";
label var secgoods_rec_3060 "# of secured goods payments 30-59 days delinq recently";
label var secgoods_rec_6090 "# of secured goods payments 60-89 days delinq recently";
label var secgoods_rec_90120 "# of secured goods payments 90-119 days delinq recently";
label var secgoods_rec_120plus "# of secured goods payments 120+ days delinq recently";
label var secgoods_rec_worse "# of secured goods payments bad debt/collection recently";

/*LEASES*/;
label var lease_open "# of lease loans opened btw last survey and now";
label var lease_clo "# of lease loans closed btw last survey and now";
label var lease_hist_ontime "# of lease payments current in past 24m";
label var lease_hist_3060 "# of lease payments 30-59 days delinq in past 24m";
label var lease_hist_6090 "# of lease payments 60-89 days delinq in past 24m";
label var lease_hist_90120 "# of lease payments 90-119 days delinq in past 24m";
label var lease_hist_120plus "# of lease payments 120+ days delinq in past 24m";
label var lease_debt "debt in lease loans";
label var lease_num "number of lease loans currently open";

label var lease_rec_ontime "# of lease payments current recently";
label var lease_rec_3060 "# of lease payments 30-59 days delinq recently";
label var lease_rec_6090 "# of lease payments 60-89 days delinq recently";
label var lease_rec_90120 "# of lease payments 90-119 days delinq recently";
label var lease_rec_120plus "# of lease payments 120+ days delinq recently";
label var lease_rec_worse "# of lease payments bad debt/collection recently";

replace crdtlim_tl=. if crdtlim_tl==.z;

gen ccard_indiv_joint_open_t=1 if dtopen~=. & dtopen~=0 & (ecoa=="I" | ecoa=="M" | ecoa=="S" | ecoa=="C") & loantype=="CCAR";
gen ccard_indiv_joint_clo_t=1 if dtclo~=. & dtclo~=0 & dtopen~=. & dtopen~=0 & (ecoa=="I" | ecoa=="M" | ecoa=="S" | ecoa=="C") & loantype=="CCAR";
bys pid year: egen ccard_indiv_joint_open=sum(ccard_indiv_joint_open_t);
bys pid year: egen ccard_indiv_joint_clo=sum(ccard_indiv_joint_clo_t);
gen ccard_indiv_joint_num=ccard_indiv_joint_open-ccard_indiv_joint_clo;

#delimit;
gen ccard_indiv_joint_debt_t=acctbal_tl if (ecoa=="I") & loantype=="CCAR" & ccard_indiv_joint_open_t==1 & ccard_indiv_joint_clo_t~=1;
gen ccard_indiv_joint_lim_t=crdtlim_tl if (ecoa=="I" | ecoa=="M" | ecoa=="S" | ecoa=="C") & loantype=="CCAR" & ccard_indiv_joint_open_t==1 & ccard_indiv_joint_clo_t~=1;
gen ccard_indiv_joint_lim_nm_t=ccard_indiv_joint_lim_t;

replace ccard_indiv_joint_debt_t=acctbal_tl*0.5 if (ecoa=="M" | ecoa=="S" | ecoa=="C") & loantype=="CCAR" & ccard_indiv_joint_open_t==1 & ccard_indiv_joint_clo_t~=1;
replace ccard_indiv_joint_lim_t=crdtlim_tl*0.5 if (ecoa=="M" | ecoa=="S" | ecoa=="C") & loantype=="CCAR" & ccard_indiv_joint_open_t==1 & ccard_indiv_joint_clo_t~=1;
replace ccard_indiv_joint_lim_t=-99999999999 if (ecoa=="I" | ecoa=="M" | ecoa=="S" | ecoa=="C") & loantype=="CCAR" & ccard_indiv_joint_open_t==1 & ccard_indiv_joint_clo_t~=1 & crdtlim_tl==.;
replace ccard_indiv_joint_lim_nm_t=ccard_indiv_joint_debt_t if (ecoa=="I" | ecoa=="M" | ecoa=="S" | ecoa=="C") & loantype=="CCAR" & ccard_indiv_joint_open_t==1 & ccard_indiv_joint_clo_t~=1 & crdtlim_tl==.;

bys pid year: egen ccard_indiv_joint_debt_new=sum(ccard_indiv_joint_debt_t);
bys pid year: egen ccard_indiv_joint_lim_new=sum(ccard_indiv_joint_lim_t);
bys pid year: egen ccard_indiv_joint_lim_nm_new=sum(ccard_indiv_joint_lim_nm_t);
replace ccard_indiv_joint_lim_new=. if ccard_indiv_joint_lim_new<0;
replace ccard_indiv_joint_lim_nm_new=. if ccard_indiv_joint_lim_nm_new<0;
replace ccard_indiv_joint_debt_new=. if ccard_indiv_joint_num==0 | ccard_indiv_joint_num==.;
replace ccard_indiv_joint_lim_new=. if ccard_indiv_joint_num==0 | ccard_indiv_joint_num==.;
replace ccard_indiv_joint_lim_nm_new=. if ccard_indiv_joint_num==0 | ccard_indiv_joint_num==.;

drop ccard_indiv_joint_debt ccard_indiv_joint_lim;
rename ccard_indiv_joint_debt_new ccard_indiv_joint_debt; 
rename ccard_indiv_joint_lim_new ccard_indiv_joint_lim;
rename ccard_indiv_joint_lim_nm_new ccard_indiv_joint_lim_nm;

drop ccard_indiv_joint_debt_t ccard_indiv_joint_lim_t ccard_indiv_joint_lim_nm_t;

/*Variable to use: (unsecrued) individual plus joint accounts*/
label var ccard_indiv_joint_num "number of indiv+joint cc currently open";
label var ccard_indiv_joint_debt "debt in indiv+joint cc";
label var ccard_indiv_joint_lim "credit limint in indiv+joint cc";
label var ccard_indiv_joint_lim_nm "credit limit (or bal if lim missing) in indiv+joint cc";

/*Creating the delinquency measures*/
egen ccard_indiv_joint_rec_ontime=rsum(ccard_indiv_rec_ontime ccard_cosign_rec_ontime), missing;
egen ccard_indiv_joint_rec_3060=rsum(ccard_indiv_rec_3060 ccard_cosign_rec_3060), missing;
egen ccard_indiv_joint_rec_6090=rsum(ccard_indiv_rec_6090 ccard_cosign_rec_6090), missing;
egen ccard_indiv_joint_rec_90120=rsum(ccard_indiv_rec_90120 ccard_cosign_rec_90120), missing;
egen ccard_indiv_joint_rec_120plus=rsum(ccard_indiv_rec_120plus ccard_cosign_rec_120plus), missing;
egen ccard_indiv_joint_rec_worse=rsum(ccard_indiv_rec_worse ccard_cosign_rec_worse), missing;

egen ccard_indiv_joint_hist_ontime=rsum(ccard_indiv_hist_ontime ccard_cosign_hist_ontime), missing;
egen ccard_indiv_joint_hist_3060=rsum(ccard_indiv_hist_3060 ccard_cosign_hist_3060), missing;
egen ccard_indiv_joint_hist_6090=rsum(ccard_indiv_hist_6090 ccard_cosign_hist_6090), missing;
egen ccard_indiv_joint_hist_90120=rsum(ccard_indiv_hist_90120 ccard_cosign_hist_90120), missing;
egen ccard_indiv_joint_hist_120plus=rsum(ccard_indiv_hist_120plus ccard_cosign_hist_120plus), missing;


label var ccard_indiv_joint_rec_ontime "# of indiv and joint cc payments current recently";
label var ccard_indiv_joint_rec_3060 "# of indiv and joint cc payments 30-59 days delinq recently";
label var ccard_indiv_joint_rec_6090 "# of indiv and joint cc payments 60-89 days delinq recently";
label var ccard_indiv_joint_rec_90120 "# of indiv and joint cc payments 90-119 days delinq recently";
label var ccard_indiv_joint_rec_120plus "# of indiv and joint cc payments 120+ days delinq recently";
label var ccard_indiv_joint_rec_worse "# of indiv and joint cc payments bad debt/collection recently";

label var ccard_indiv_joint_hist_ontime "# of indiv and joint cc payments current in past 24m";
label var ccard_indiv_joint_hist_3060 "# of indiv and joint cc payments 30-59 days delinq in past 24m";
label var ccard_indiv_joint_hist_6090 "# of indiv and joint cc payments 60-89 days delinq in past 24m";
label var ccard_indiv_joint_hist_90120 "# of indiv and joint cc payments 90-119 days delinq in past 24m";
label var ccard_indiv_joint_hist_120plus "# of indiv and joint cc payments 120+ days delinq in past 24m";


preserve;
keep tr_am ccard_indiv_joint_lim_nm;
recode tr_am 270/340=1 341/410=2 411/480=3 481/550=4 551/620=5 621/690=6 691/760=7 761/830=8 831/912=9;
collapse (mean) ccard_indiv if tr_am~=. & ccard_indiv>0 & ccard_indiv<., by(tr_am);
export excel ccard_* tr_am
using "`pathtables'/CCard_Limit_By_CreditScore.xlsx", firstrow(variables) replace;
restore;


/*For the timebeing, I will drop the terms and hicredit variables, which are variables at the tradeline level*/
drop dtopen dtveri dtclo ecoa ecoa_tl crdtlim_tl interest loantype auto_open_tl auto_clo_tl student_open_tl student_clo_tl student_debt_tl mortgage_open_tl mortgage_clo_tl ccard_indiv_joint_open_t ccard_indiv_joint_clo_t terms_tl hicredit_tl;

drop auto_open auto_clo mortgage_open mortgage_clo student_open student_clo ccard_indiv_open ccard_indiv_clo;
sort pid year;

by pid year: keep if _n==1;

save "`out'/tutemp_tomerge.dta", replace;
